﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SundUnderviser.ModelLayer;

namespace SundUnderviser.DBLayer
{
    public class DBLaboratorieskema
    {
         private SqlCommand dbCmd = null;
        
        public List<Laboratorie> FindLabskema(int pnr)
        {
            string sql = @"select patientnr, nr,dato, vagt, hermoglobin, leukocytter, " +
                          "thrombocytter, koagulationsfakor, ph, baseexcess, oxygen, " +
                          " hydrogencarbonation, kaliumion, natriumion, creatinium, " +
                          " carbamid,albumin, cReakticprotein, glykose6, glykose12, " +
                          " glykose17, glykose21, antaldage " +
                           " from laboratorie " +
                           " where patientnr  = " + pnr
                           + " order by dato desc";


            Console.WriteLine("sql stmt = " + sql);

            dbCmd = DBConnection.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();
            List<Laboratorie> labList = new List<Laboratorie>();
            Laboratorie labObj;

            while (dbReader.Read())
            {

                labObj = new Laboratorie();
                Console.WriteLine("antal felter " + dbReader.FieldCount);
                Patient patientObj = new Patient();
                patientObj.Nr = Convert.ToInt32(dbReader["patientnr"].ToString());
                labObj.Patient = patientObj;
                labObj.Nr = Convert.ToInt32(dbReader["nr"].ToString());
                labObj.Dato = DateTime.Parse(dbReader["dato"].ToString());
                labObj.Vagt = dbReader["vagt"].ToString();
                labObj.Hemoglobin = dbReader["hermoglobin"].ToString();
                labObj.Leukocytter = dbReader["leukocytter"].ToString();               
                labObj.Thrombocytter = dbReader["thrombocytter"].ToString();
                labObj.Koagulationsfaktor = dbReader["koagulationsfakor"].ToString();
                labObj.Ph = dbReader[" ph"].ToString();
                labObj.BaseExcess = dbReader["baseexcess"].ToString();
                labObj.Oxygen = dbReader["oxygen"].ToString();
                labObj.Hydrogencarbonation = dbReader["hydrogencarbonation"].ToString();
                labObj.KaliumIon = dbReader["kaliumion"].ToString();
                labObj.NatriumIon = dbReader["natriumion"].ToString();
                labObj.Ph = dbReader["ph"].ToString();
                labObj.Creatinium = dbReader["creatinium"].ToString();


                labObj.Carboamid= dbReader["carbamid"].ToString();
                labObj.Albumin = dbReader["albumin"].ToString();
                labObj.CReaktivProtein = dbReader["cReakticprotein"].ToString();

                labObj.Glykose6 = dbReader["glykose6"].ToString();
                labObj.Glykose12 = dbReader["glykose12"].ToString();
                labObj.Glykose17 = dbReader["glykose17"].ToString();
                labObj.Glykose21 = dbReader["glykose21"].ToString();

                labObj.AntalDage = Convert.ToInt32(dbReader["antaldage"].ToString());

                labList.Add(labObj);


            }
            dbReader.Close();
            DBConnection.Close();
            return labList;
        }



        public int InsertLab(Laboratorie labObj)
        {
            int rc = -1;
            SqlCommand dbCmd = null;
            try
            { // tildel naeste nr
                labObj.Nr = 1;
                // 
                string sql = "select * from laboratorie where patientnr = " + labObj.Patient.Nr;
                SqlCommand dbCmd1 = DBConnection.GetDbCommand(sql);
                IDataReader dbReader;
                dbReader = dbCmd1.ExecuteReader();
                if (dbReader.Read())
                {
                    Console.WriteLine("har fundet nogle");
                    String sql2 = "select max(nr) as nextnr from laboratorie where patientnr = " + labObj.Patient.Nr;
                    DBConnection.Close();
                    SqlCommand dbCmd2 = DBConnection.GetDbCommand(sql2);
                    dbReader = dbCmd2.ExecuteReader();
                    Console.WriteLine("næstenr labObj 1: " + labObj.Nr);
                    if (dbReader.Read())
                    {
                        Console.WriteLine("tildel ny nr");
                        if (dbReader["nextnr"] != null)
                        {
                            labObj.Nr = Convert.ToInt32(dbReader["nextnr"].ToString()) + 1;
                            Console.WriteLine("næstenr labObj 2: " + labObj.Nr);
                        }
                    }

                }

                DBConnection.Close();
                Console.WriteLine("Insert laboratorie dato : " + labObj.Dato);
                string myDato = "" + labObj.Dato.Year;
                if (labObj.Dato.Month >= 10)
                {
                    myDato = myDato + labObj.Dato.Month;
                }
                else
                {
                    myDato = myDato + "0" + labObj.Dato.Month;
                }
                              
                if (labObj.Dato.Day >= 10)
                {
                    myDato = myDato + labObj.Dato.Day;
                }
                else
                {
                    myDato = myDato + "0" + labObj.Dato.Day;
                }
               
                Console.WriteLine("Insert laboratorie mydato : " + myDato);
                string sqlInsert = "insert into laboratorie (patientnr, nr,dato, vagt, hermoglobin, leukocytter, "+
                                    "thrombocytter, koagulationsfakor, ph, baseexcess, oxygen, " +
                                    "hydrogencarbonation, kaliumion, natriumion, creatinium, " +
                                    "carbamid,albumin, cReakticprotein, glykose6, glykose12, " +
                                    " glykose17, glykose21, antaldage)" +
                                    "values (" + labObj.Patient.Nr + ","
                                               + labObj.Nr + ",'" 
                                               + myDato + "','" 
                                               + labObj.Vagt + "','" 
                                               + labObj.Hemoglobin + "','" 
                                               + labObj.Leukocytter + "','" 
                                               + labObj.Thrombocytter + "','"
                                               + labObj.Koagulationsfaktor+ "','"
                                               + labObj.Ph + "','"
                                               + labObj.BaseExcess + "','"
                                               + labObj.Oxygen + "','"
                                               + labObj.Hydrogencarbonation + "','"
                                               + labObj.KaliumIon + "','"
                                               + labObj.NatriumIon + "','"
                                               + labObj.Creatinium + "','"
                                               + labObj.Carboamid + "','"
                                               + labObj.Albumin + "','"
                                               + labObj.CReaktivProtein + "','"
                                               + labObj.Glykose6 + "','"
                                               + labObj.Glykose12 + "','"

                                               + labObj.Glykose17 + "','"
                                               + labObj.Glykose21 + "'," 



                                               + labObj.AntalDage + ")";
                Console.WriteLine(sqlInsert);
                dbCmd = DBConnection.GetDbCommand(sqlInsert);


                rc = dbCmd.ExecuteNonQuery();


            }
            catch (Exception insertE)
            {
                Console.WriteLine("Fejl i insert laboratorieskaema");
                Console.WriteLine(insertE);
            }
            DBConnection.Close();
            return rc;
        }
        public int findMaxAntalDage(int pnr)
        {
            int maxDage = 0;
            string sql = @"select count(antaldage) as antal,max(antaldage)  as maxDage from laboratorie " +
                           " where patientnr  = " + pnr;


            Console.WriteLine("sql stmt = " + sql);

            dbCmd = DBConnection.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            if (dbReader.Read())
            {
                int antal = Convert.ToInt32(dbReader["antal"].ToString());
                if (antal > 0)
                {
                    maxDage = Convert.ToInt32(dbReader["maxDage"].ToString());
                    Console.WriteLine("antal felter " + dbReader.FieldCount);
                }
                else
                {
                    maxDage = 0;
                }

            }
            dbReader.Close();
            DBConnection.Close();
            return maxDage;
        }
    
    }
}
